﻿CREATE PROCEDURE [SSIS].[CreateProjectEnvironmentReference](
	@SolutionFolderName sysname,
	@ProjectName sysname,
	@EnvironmentName sysname,
	@EnvironmentReferenceID bigint OUTPUT
)
AS
BEGIN
	--get the project id
	DECLARE @ProjectID bigint;
	SELECT
		@ProjectID = prj.[project_id]
	FROM [$(SSISDB)].[catalog].[projects] AS prj
	INNER JOIN [$(SSISDB)].[catalog].[folders] AS fld
		ON (fld.[folder_id] = prj.[folder_id])
		AND(fld.[name] = @SolutionFolderName)
	WHERE (prj.[name] = @ProjectName);

	IF (@ProjectID IS NULL)
		BEGIN
			RAISERROR(N'%s is not a valid project within %s',16,1,@ProjectName,@SolutionFolderName);
			RETURN;
		END

	--check if the reference exists
	DECLARE @RelativeReferenceLocation char(1) = 'R';

	SELECT @EnvironmentReferenceID = [reference_id]
	FROM [$(SSISDB)].[catalog].[environment_references] AS ref
	WHERE  (ref.[project_id] = @ProjectID)
		AND(ref.[reference_type] = @RelativeReferenceLocation)
		AND(ref.[environment_name] = @EnvironmentName)

	IF (@EnvironmentReferenceID IS NOT NULL)
		RETURN;

	--add the reference
	EXEC [$(SSISDB)].[catalog].[create_environment_reference]
		@folder_name = @SolutionFolderName,
		@project_name = @ProjectName,
		@reference_type = @RelativeReferenceLocation,
		@environment_name = @EnvironmentName,
		@reference_id = @EnvironmentReferenceID OUTPUT;

	RETURN(0);
END
GO
EXECUTE sp_addextendedproperty @name = N'Description', @value = N'This procedure will create a relative reference in the specified project to the specified solution environment.
This will enable SSIS projects and packages to be configured with default values that are stored in the environment variables container.', @level0type = N'SCHEMA', @level0name = N'SSIS', @level1type = N'PROCEDURE', @level1name = N'CreateProjectEnvironmentReference';

